from flask import Flask, request, jsonify
import requests
# from functions import *
import pymysql

app = Flask(__name__)
# server_prompt_url = None


@app.route('/hello/<id>')
def hello(id):
    print(id)
    print(type(int(id)))
    return f"hello, world,{id}"


# 指定服务所使用的prompt，传入的参数是项目id和模板id
@app.route('/set_project_prompt', methods=['POST'])
def target_prompt():
    data = request.get_json()
    project_id = data['project_id']
    prompt_id = data['prompt_id']

    # 根据project_id从数据库中拿到项目的url
    url = get_prompt_url(project_id)
    print("url:" + url)
    # 拼接路径
    url = url + '/setPrompt'
    # 根据prompt_id获取content
    prompt_content = get_prompt_byId(prompt_id)
    response = {}

    try:
        # 发送请求，指定项目所用的prompt
        print(prompt_content)
        requests.post(url, json=prompt_content)
        # 将结果写入数据库
        set_prompt(project_id, prompt_id)
        response['message'] = "ok"
    except:
        response['message'] = "Set server prompt failed"
    return response


@app.route('/update_prompt', methods=['POST'])
def update():
    data = request.get_json()
    return update_prompt(data)


@app.route('/delete_prompt', methods=['DELETE'])
def delete():
    data = request.get_json()
    return delete_prompt(data)


@app.route('/create_prompt', methods=['POST'])
def insert():
    data = request.get_json()
    # print(type(data))
    return insert_prompt(data)


@app.route('/query_all')
def query():
    data = request.get_json()
    user_name = data['user_name']
    res = query_all2(user_name)
    return jsonify(res)


# @app.route('/query_page/<page_num>')
# def page(page_num):
#     # tmp = request.args.get('page_num')
#     page_num = int(page_num)
#     res = page_query(page_num)
#     res = list(res)
#     response = jsonify(res)
#     return response
#
#
# # 根据title模糊查询
# @app.route('/match_query/<title>')
# def match(title):
#     # text = request.args.get('title')
#     response = match_query(title)
#     response = jsonify(response)
#     return response

db = None
def getConnection():
    # const (
    #     UserName = "root"
    # Password = "123456"
    # Host     = "10.233.55.208"
    # Port     = "3306"
    # Dbname   = "heima"
    # Timeout  = "10s"
    # )
    # db = pymysql.connect(host="10.233.55.208",
    #                      user="root",
    #                      password="123456",
    #                      database="heima")
    # return db

    db = pymysql.connect(host="10.233.36.189",
                         user="root",
                         password="123456",
                         database="heima")
    return db


def close_database_connection():
    global db
    if db is not None:
        # 关闭数据库连接
        db.close()
        db = None


def get_prompt_byId(prompt_id):
    db = getConnection()
    cursor = db.cursor()
    sql = f'''select content from prompts where id = {prompt_id}'''
    print(sql)
    cursor.execute(sql)
    row = cursor.fetchone()
    if row:
        print(row[0])
        res = {"related_template": row[0]}
        print(res)
        return res
    cursor.close()
    db.close()


def get_prompt_url(project_id):
    db = getConnection()
    cursor = db.cursor()
    sql = f'''select url from projects where id = "{project_id}"'''
    cursor.execute(sql)
    row = cursor.fetchone()
    if row:
        print(row[0])
        return row[0]
    cursor.close()
    db.close()


def set_prompt(project_id, prompt_id):
    db = getConnection()
    cursor = db.cursor()
    db.begin()
    sql = f'''update projects set prompt_id = {prompt_id} where project_id = "{project_id}"'''
    cursor.execute(sql)
    db.commit()


# TODO 插入成功
def insert_user_prompt_relation(db, user_name, prompt_id):
    # db = getConnection()
    cursor = db.cursor()
    # print("===========")
    print(user_name, prompt_id)
    # sql = f'''insert into user_prompt_relation (user_id, prompt_id) values ({user_id}, {prompt_id})'''
    sql = f'''insert into user_prompt_relations (user_name, prompt_id) values ('{user_name}', {prompt_id})'''
    print("sql：" + sql)
    try:
        db.begin()
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(str(e))
        db.rollback()
    cursor.close()


# TODO ok
def insert_prompt(data):
    db = getConnection()
    user_name = data['user_name']
    title = data['title']
    # 1表示自定义
    type = 1
    content = data['content']
    # server_name = data['server_name']

    sql1 = f'''insert into prompts (title, type, content) values ('{title}', {type}, '{content}')'''
    print(sql1)
    cursor = db.cursor()
    response = {}
    try:
        db.begin()
        cursor.execute(sql1)
        # 获取新插入的prompt_id
        db.commit()
        cursor.execute("select last_insert_id()")
        last_id = cursor.fetchone()
        prompt_id = int(last_id[0])
        print(prompt_id)
        sql2 = f'''select user_id from users where user_name = "{user_name}"'''
        cursor.execute(sql2)
        res = cursor.fetchone()
        user_name = ""
        for row in res:
            user_name = row
        insert_user_prompt_relation(db, user_name, prompt_id)
        response['message'] = "ok"
    except Exception as e:
        db.rollback()
        print(f"Creation failed: {str(e)}")
        response['message'] = "Creation failed"
    finally:
        cursor.close()
        db.close()
        return response


# 更新prompt
def update_prompt(data):
    db = getConnection()
    cursor = db.cursor()
    id = data['id']
    title = data['title']
    content = data['content']
    sql = f'''update prompts set title = '{title}', content = '{content}' where id = {id}'''
    print(sql)
    response = {}
    try:
        db.begin()
        cursor.execute(sql)
        db.commit()
        response['message'] = "ok"
    except Exception as e:
        db.rollback()
        print(f"Transaction failed: {str(e)}")
        response['message'] = "Update failed"
        return "failed"
    finally:
        cursor.close()
        return response


# 根据promptId删除对应prompt表和user_prompt_relation中的记录
def delete_prompt(data):
    db = getConnection()
    prompt_id = data['prompt_id']
    sql1 = f'''DELETE FROM prompts where id = {prompt_id}'''
    print(sql1)
    sql2 = f'''delete from user_prompt_relations where prompt_id = {prompt_id}'''
    print(sql2)
    cursor = db.cursor()
    response = {}
    try:
        db.begin()
        cursor.execute(sql1)
        cursor.execute(sql2)
        db.commit()
        response['message'] = "ok"
    except Exception as e:
        db.rollback()
        print(f"Transaction failed: {str(e)}")
        response['message'] = "Deletion failed"
    finally:
        cursor.close()
        # db.close()
        return response


# 根据user_id返回这个用户拥有的prompt和全部预置的prompt
def query_all(uid):
    db = getConnection()
    sql = f'''SELECT * from prompts where id in 
    (select prompt_id from user_prompt_relations where user_id = {uid}) 
    UNION SELECT * from prompts where type = 0'''
    print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    res = cursor.fetchall()

    cursor.close()
    return res


def query_all2(user_name):
    db = getConnection()
    sql = f'''SELECT * from prompts where id in 
    (select prompt_id from user_prompt_relations where user_name = '{user_name}') 
    UNION SELECT * from prompts where type = 0'''
    print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    res = cursor.fetchall()

    for row in res:
        print(row)

    cursor.close()
    db.close()
    return res

def get_user_prompt_relation():
    db = getConnection()
    cursor = db.cursor()
    sql = '''select * from user_prompt_relations'''
    cursor.execute(sql)
    res = cursor.fetchall()
    for row in res:
        print(row[0])
        print(row[1])
        print(row[2])
    cursor.close()


if __name__ == '__main__':
    app.run(host="192.168.223.103", port=8090)
    # app.run(port=8080)
